#coding=utf-8
from framework.db.mysql import BaseDb
from tornado import gen
from lib import log

class Box(BaseDb):

    @gen.coroutine
    def select_box(self, school_id, page, offset):
        sql = '''SELECT Fid, Fname, Fmin_price, Fstatus
                , Fschool_id, Fcanteen_id, Fremark, Fcreate_time
                FROM tb_box 
                WHERE Fschool_id = %s 
                LIMIT %s, %s'''
        count_sql = 'SELECT count(Fid) FROM tb_box a WHERE Fschool_id = %s'

        ret = yield self.executejson(sql, (school_id, page, offset))
        count = yield self.execute(count_sql, (school_id))

        raise gen.Return((ret, count[0][0]))

    @gen.coroutine
    def find_box(self, Fid):
        sql = '''SELECT Fid, Fname, Fmin_price, Fstatus
                , Fschool_id, Fcanteen_id, Fremark, Fcreate_time
                FROM tb_box 
                WHERE Fid = %s '''

        ret = yield self.executejson(sql, (Fid))

        if len(ret) >= 1:
            raise gen.Return(ret[0])
        else:
            raise gen.Return({})

    @gen.coroutine
    def insert_box(self, box_info):

        ret = yield self.insert("tb_box", box_info)

        self.commit()

        raise gen.Return(ret)

    @gen.coroutine
    def del_box(self, Fid):
        sql = 'DELETE FROM tb_box WHERE Fid = %s'

        ret = yield self.upsert_commit(sql, (Fid))

        raise gen.Return(ret)

    @gen.coroutine
    def update_box(self, Fid, arg):
        box_sql = '''UPDATE tb_box SET %s WHERE Fid = %s'''

        update_data = ''
        for i in arg.keys():
            update_data += i + '=%s,'

        box_sql = box_sql % (update_data[0:-1], '%s')

        data = arg.values()
        data.append(Fid)

        ret = yield self.upsert(box_sql, tuple(data))

        self.commit()

        raise gen.Return(ret)

    @gen.coroutine
    def set_status(self, Fid, status):
        sql = 'UPDATE tb_box SET Fstatus = %s WHERE Fid = %s'

        ret = yield self.upsert_commit(sql, (status, Fid))

        raise gen.Return(ret)

    @gen.coroutine
    def set_istatus(self, status, *ids):
        sql = 'UPDATE tb_box SET Fstatus = %s WHERE Fid in %s'

        ret = yield self.upsert_commit(sql, (status, ids))

        raise gen.Return(ret)